package com.ylkj.wx.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import com.ylkj.common.base.BaseDaoHibernate;
import com.ylkj.wx.dao.QuestionInfoTableDAO;
import com.ylkj.wx.dto.QuestionListDTO;

@Repository
public class QuestionInfoTableDAOImpl extends BaseDaoHibernate implements QuestionInfoTableDAO {

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<QuestionListDTO> findQuestionInfoTable(int pageNum, int pageSize) {
    	List<QuestionListDTO> list = (List<QuestionListDTO>)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
	    		List ret = null;
	    		//使用sql查询，防止使用Entity带来的过多关联查询，导致性能的下降
	    		StringBuffer sb = new StringBuffer();
	    		sb.append("SELECT a.`id` as questionId, a.`content` AS content, b.`photo_url` AS photoUrl");
	    		sb.append(" FROM `question_info` a, `user_info` b ");
	    		sb.append(" WHERE a.`phr_code`=b.`phr_code`");

	            ret = session.createSQLQuery(sb.toString()).addScalar("questionId").addScalar("content")
	            		.addScalar("photoUrl")
//	    		.addScalar(registerCode,Hibernate.STRING)
	    		.setResultTransformer(org.hibernate.transform.Transformers.aliasToBean(QuestionListDTO.class))
//	    		.setParameter("registerCode",registerCode)
	    		.list();
	    		return ret;
	    	}
    	});
    	return list;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<QuestionListDTO> findAnswerInfo(final List<Integer> queIdList) {
    	List<QuestionListDTO> list = (List<QuestionListDTO>)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
	    		List ret = null;
	    		//使用sql查询，防止使用Entity带来的过多关联查询，导致性能的下降
	    		StringBuffer sb = new StringBuffer();
	    		sb.append("SELECT a.`que_id` as questionId,b.`lawyer_name` AS lawyerName, b.`province` AS province,b.`city` AS city ,b.`area` AS areaAddr,");
	    		sb.append(" b.`lawfirm_name` AS lawfirmName,c.`photo_url` AS photoUrl,a.`content` AS anwserContent,a.`busi_type_code` as busiTypeCode ");
	    		sb.append(" FROM `im_record` a,`lawyer_info` b,`user_info` c ");
	    		sb.append(" WHERE a.`busi_type_code` = '10' AND a.`sender_phr_code` = b.`phr_code` AND a.`sender_phr_code` = c.`phr_code` ");
	    		sb.append(" AND a.`que_id` IN (:queId) ");

	            ret = session.createSQLQuery(sb.toString()).addScalar("questionId").addScalar("lawyerName")
	            		.addScalar("province").addScalar("city").addScalar("areaAddr")
	            		.addScalar("lawfirmName").addScalar("photoUrl").addScalar("anwserContent")
	            		.addScalar("busiTypeCode")
//	    		.addScalar(registerCode,Hibernate.STRING)
	    		.setResultTransformer(org.hibernate.transform.Transformers.aliasToBean(QuestionListDTO.class))
//	    		.setParameter("registerCode",registerCode)
	    		.setParameterList("queId", queIdList)
	    		.list();
	    		return ret;
	    	}
    	});
    	return list;
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<QuestionListDTO> findAnswerInfoById(final Integer queId) {
    	List<QuestionListDTO> list = (List<QuestionListDTO>)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
	    		List ret = null;
	    		//使用sql查询，防止使用Entity带来的过多关联查询，导致性能的下降
	    		StringBuffer sb = new StringBuffer();
	    		sb.append("SELECT a.`que_id` as questionId,b.`lawyer_name` AS lawyerName, b.`province` AS province,b.`city` AS city ,b.`area` AS areaAddr,");
	    		sb.append(" b.`lawfirm_name` AS lawfirmName,c.`photo_url` AS photoUrl,a.`content` AS anwserContent,a.`busi_type_code` as busiTypeCode ");
	    		sb.append(" FROM `im_record` a,`lawyer_info` b,`user_info` c ");
	    		sb.append(" WHERE a.`sender_phr_code` = b.`phr_code` AND a.`sender_phr_code` = c.`phr_code` ");
	    		sb.append(" AND a.`que_id` IN (:queId) ");

	            ret = session.createSQLQuery(sb.toString()).addScalar("questionId").addScalar("lawyerName")
	            		.addScalar("province").addScalar("city").addScalar("areaAddr")
	            		.addScalar("lawfirmName").addScalar("photoUrl").addScalar("anwserContent")
	            		.addScalar("busiTypeCode")
//	    		.addScalar(registerCode,Hibernate.STRING)
	    		.setResultTransformer(org.hibernate.transform.Transformers.aliasToBean(QuestionListDTO.class))
//	    		.setParameter("registerCode",registerCode)
	    		.setParameter("queId", queId)
	    		.list();
	    		return ret;
	    	}
    	});
    	return list;
	}
	
	
	
//	@SuppressWarnings({ "unchecked", "rawtypes" })
//	@Override
//	public List<QuestionListDTO> findQuestionInfoTable(int pageNum, int pageSize) {
//    	List<QuestionListDTO> list = (List<QuestionListDTO>)getHibernateTemplate().execute(new HibernateCallback() {
//			@Override
//			public Object doInHibernate(Session session)
//					throws HibernateException, SQLException {
//	    		List ret = null;
//	    		//使用sql查询，防止使用Entity带来的过多关联查询，导致性能的下降
//	    		StringBuffer sb = new StringBuffer();
//	    		sb.append("SELECT a.`content` as content, c.`lawyer_name` as lawyerName,");
//	    		sb.append(" c.`province` as province,c.`city` as city,c.`area` as areaAddr, ");
//	    		sb.append(" c.`lawfirm_name` as lawfirmName,b.`photo_url` as photoUrl,d.`anwser_content` as anwserContent");
//	    		sb.append(" FROM `question_info` a,`user_info` b,`lawyer_info` c,`answer_info` d ");
//	    		sb.append(" WHERE a.`id` = d.`que_id` AND d.`phr_code` = c.`phr_code` AND d.`phr_code` = b.`phr_code`");  		
//
//	            ret = session.createSQLQuery(sb.toString()).addScalar("lawyerName")
//	            		.addScalar("content").addScalar("province").addScalar("city").addScalar("areaAddr")
//	            		.addScalar("lawfirmName").addScalar("photoUrl").addScalar("anwserContent")
////	    		.addScalar(registerCode,Hibernate.STRING)
//	    		.setResultTransformer(org.hibernate.transform.Transformers.aliasToBean(QuestionListDTO.class))
////	    		.setParameter("registerCode",registerCode)
//	    		.list();
//	    		return ret;
//	    	}
//    	});
//    	return list;
//	}

}
